package com.bowlong.third.xss;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.bowlong.json.JSON;
import com.bowlong.lang.NumEx;
import com.bowlong.util.DateEx;

@SuppressWarnings({ "rawtypes" })
public class XSS {
	public static final String TYPE_BOOLEAN = "boolean";
	public static final String TYPE_DOUBLE = "double";
	public static final String TYPE_INT = "int";
	public static final String TYPE_LONG = "long";
	public static final String TYPE_STRING = "String";
	public static final String TYPE_DATE = "Date";
	public static final String TYPE_MAP = "Map";
	public static final String TYPE_LIST = "List";

	public static final OPCPackage openPackage(final String fn)
			throws IOException, InvalidFormatException {
		final File fp = new File(fn);
		return openPackage(fp);
	}

	public static final OPCPackage openPackage(final File fp)
			throws IOException, InvalidFormatException {
		OPCPackage pkg = OPCPackage.open(fp);
		return pkg;
	}

	public static final XSSFWorkbook openWorkbook(final String fn)
			throws IOException, InvalidFormatException {
		final File fp = new File(fn);
		return openWorkbook(fp);
	}

	public static final XSSFWorkbook openWorkbook(final File fp)
			throws IOException, InvalidFormatException {
		OPCPackage pkg = OPCPackage.open(fp);
		XSSFWorkbook wb = new XSSFWorkbook(pkg);
		return wb;
	}

	public static final XSSFWorkbook openWorkbook(final OPCPackage pkg)
			throws IOException, InvalidFormatException {
		XSSFWorkbook wb = new XSSFWorkbook(pkg);
		return wb;
	}

	public static final int sheetNum(final XSSFWorkbook wb) {
		return wb.getNumberOfSheets();
	}

	public static final XSSFSheet getSheet(final XSSFWorkbook wb,
			final String name) {
		XSSFSheet sheet = wb.getSheet(name);
		return sheet;
	}

	public static final int rowNum(final XSSFSheet sheet) {
		return sheet.getLastRowNum(); // 行数
	}

	public static final int cellNum(final XSSFSheet sheet, int rownum) {
		XSSFRow row = sheet.getRow(rownum);
		return cellNum(row);
	}

	public static final int cellNum(final XSSFSheet sheet) {
		int rownum = 0;
		XSSFRow row = sheet.getRow(rownum);
		return cellNum(row);
	}

	public static final int cellNum(final XSSFRow row) {
		return row.getLastCellNum(); // 列数
	}

	public static final XSSFSheet[] sheets(final XSSFWorkbook wb) {
		int sheetNum = sheetNum(wb);
		XSSFSheet[] r2 = new XSSFSheet[sheetNum];
		for (int i = 0; i < sheetNum; i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			if (sheet == null)
				break;
			r2[i] = sheet;
		}
		return r2;
	}

	public static final Object getObject(final XSSFSheet sheet,
			final String type, final int rownum, final int column) {
		XSSFRow row = sheet.getRow(rownum);
		return getObject(row, type, column);
	}

	public static final Object getObject(final XSSFRow row, final String type,
			final int column) {
		XSSFCell cell = row.getCell(column);
		return getObject(cell, type);
	}

	public static final Object getObject(final XSSFCell cell, final String type) {
		if (type.equals(TYPE_BOOLEAN)) {
			return getBool(cell);
		} else if (type.equals(TYPE_STRING)) {
			return getString(cell);
		} else if (type.equals(TYPE_INT)) {
			return getInt(cell);
		} else if (type.equals(TYPE_LONG)) {
			return getLong(cell);
		} else if (type.equals(TYPE_DOUBLE)) {
			return getDouble(cell);
		} else if (type.equals(TYPE_DATE)) {
			return getDate(cell, DateEx.fmt_yyyy_MM_dd_HH_mm_ss);
		} else if (type.equals(TYPE_MAP)) {
			return getMap(cell);
		} else if (type.equals(TYPE_LIST)) {
			return getList(cell);
		}
		return getString(cell);
	}

	public static final String getString(final XSSFRow row, int i) {
		if (row == null)
			return "";

		XSSFCell cell = row.getCell(i);
		return getString(cell);
	}

	private static FormulaEvaluator formula;
	public static final FormulaEvaluator getFormula(final XSSFCell v) {
		if(formula != null)
			return formula;
		XSSFRow row = v.getRow();
		return getFormula(row);
	}

	public static final FormulaEvaluator getFormula(XSSFRow row) {
		if(formula != null)
			return formula;
		XSSFSheet sheet = row.getSheet();
		return getFormula(sheet);
	}

	public static final FormulaEvaluator getFormula(XSSFSheet sheet) {
		if(formula != null)
			return formula;
		XSSFWorkbook wb = sheet.getWorkbook();
		return getFormula(wb);
	}

	public static final FormulaEvaluator getFormula(XSSFWorkbook wb) {
		if(formula != null)
			return formula;
		FormulaEvaluator formula = wb.getCreationHelper()
				.createFormulaEvaluator();
		return formula;
	}

	public static final String getString(final XSSFCell v) {
		try {
			if (v == null)
				return "";
			int cellType = v.getCellType();
			switch (cellType) {
			case XSSFCell.CELL_TYPE_BLANK:
				return "";
			case XSSFCell.CELL_TYPE_NUMERIC:
				return "" + v.getNumericCellValue();
			case XSSFCell.CELL_TYPE_STRING:
				return v.getStringCellValue().trim();
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return "" + v.getBooleanCellValue();
			case XSSFCell.CELL_TYPE_FORMULA:
				FormulaEvaluator formula = getFormula(v);
				Cell cell = formula.evaluateInCell(v);
				return getString(cell);
			case XSSFCell.CELL_TYPE_ERROR:
				return v.getErrorCellString();
			default:
				return "unknow.";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public static final String getString(final Cell v) {
		try {
			if (v == null)
				return "";
			int cellType = v.getCellType();
			switch (cellType) {
			case XSSFCell.CELL_TYPE_BLANK:
				return "";
			case XSSFCell.CELL_TYPE_NUMERIC:
				return "" + v.getNumericCellValue();
			case XSSFCell.CELL_TYPE_STRING:
				return v.getStringCellValue().trim();
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return "" + v.getBooleanCellValue();
			default:
				return "unknow.";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public static final String getString(final XSSFSheet sheet, final int row,
			final int column) {
		XSSFRow c = sheet.getRow(row);
		return getString(c, column);
	}

	public static final String getComment(final XSSFRow row, int i) {
		if (row == null)
			return "";
		XSSFCell cell = row.getCell(i);
		return getComment(cell);
	}

	public static final String getComment(final XSSFCell v) {
		try {
			if (v == null)
				return "";
			XSSFComment comment = v.getCellComment();
			if (comment == null)
				return "";
			XSSFRichTextString rich = comment.getString();
			if (rich == null)
				return "";
			return rich.getString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public static final String getComment(final XSSFSheet sheet, final int row,
			final int column) {
		XSSFRow c = sheet.getRow(row);
		return getComment(c, column);
	}

	public static int getInt(final XSSFSheet sheet, final int rownum,
			final int column) {
		XSSFRow row = sheet.getRow(rownum);
		return getInt(row, column);
	}

	public static int getInt(final XSSFRow row, int i) {
		if (row == null)
			return -1;
		XSSFCell cell = row.getCell(i);
		return getInt(cell);
	}

	public static int getInt(final XSSFCell cell) {
		try {
			double v = getDouble(cell);
			return (int) v;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}

	public static long getLong(final XSSFSheet sheet, final int rownum,
			final int column) {
		XSSFRow row = sheet.getRow(rownum);
		return getLong(row, column);
	}

	public static long getLong(final XSSFRow row, int i) {
		if (row == null)
			return 0;

		XSSFCell cell = row.getCell(i);
		return getLong(cell);
	}

	public static long getLong(final XSSFCell cell) {
		try {
			double v = getDouble(cell);
			return (long) v;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}

	public static double getDouble(final XSSFSheet sheet, final int rownum,
			final int column) {
		XSSFRow row = sheet.getRow(rownum);
		return getDouble(row, column);
	}

	public static double getDouble(final XSSFRow row, int i) {
		if (row == null)
			return 0;

		XSSFCell cell = row.getCell(i);
		return getDouble(cell);
	}

	public static double getDouble(final XSSFCell cell) {
		try {
			String v = getString(cell);
			return NumEx.stringToDouble(v, -1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}

	public static boolean getBoolean(final XSSFSheet sheet, final int row,
			final int column) {
		return getBool(sheet, row, column);
	}

	public static boolean getBoolean(final XSSFRow row, int i) {
		if (row == null)
			return false;

		return getBool(row, i);
	}

	public static boolean getBoolean(final XSSFCell cell) {
		return getBool(cell);
	}

	public static boolean getBool(final XSSFSheet sheet, final int rownum,
			final int column) {
		XSSFRow row = sheet.getRow(rownum);
		return getBool(row, column);
	}

	public static boolean getBool(final XSSFRow row, int i) {
		if (row == null)
			return false;

		XSSFCell cell = row.getCell(i);
		return getBool(cell);
	}

	public static boolean getBool(final XSSFCell cell) {
		try {
			String v = getString(cell).toLowerCase();
			return v.equals("true") || v.equals("1");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public static Date getDate(final XSSFSheet sheet, final int rownum,
			final int column) {
		String fmt = DateEx.fmt_yyyy_MM_dd_HH_mm_ss;
		return getDate(sheet, rownum, column, fmt);
	}

	public static Date getDate(final XSSFSheet sheet, final int rownum,
			final int column, String fmt) {
		XSSFRow row = sheet.getRow(rownum);
		return getDate(row, column, fmt);
	}

	public static Date getDate(XSSFRow row, int i) {
		String fmt = DateEx.fmt_yyyy_MM_dd_HH_mm_ss;
		return getDate(row, i, fmt);
	}

	public static Date getDate(XSSFRow row, int i, String fmt) {
		XSSFCell cell = row.getCell(i);
		return getDate(cell, fmt);
	}

	public static Date getDate(XSSFCell cell) {
		String fmt = DateEx.fmt_yyyy_MM_dd_HH_mm_ss;
		return getDate(cell, fmt);
	}

	public static Date getDate(XSSFCell cell, String fmt) {
		try {
			String v = getString(cell);
			return DateEx.parse(v, fmt);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new Date();
	}

	public static final Map getMap(final XSSFSheet sheet, final int rownum,
			final int column) {
		XSSFRow row = sheet.getRow(rownum);
		return getMap(row, column);
	}

	public static final Map getMap(final XSSFRow row, int i) {
		if (row == null)
			return null;

		XSSFCell cell = row.getCell(i);
		return getMap(cell);
	}

	public static final Map getMap(final XSSFCell cell) {
		try {
			String str = getString(cell);
			return JSON.parseMap(str);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static final List getList(final XSSFSheet sheet, final int rownum,
			final int column) {
		XSSFRow row = sheet.getRow(rownum);
		return getList(row, column);
	}

	public static final List getList(final XSSFRow row, int i) {
		if (row == null)
			return null;

		XSSFCell cell = row.getCell(i);
		return getList(cell);
	}

	public static final List getList(final XSSFCell cell) {
		try {
			String str = getString(cell);
			return (List) JSON.parseList(str);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
}
